Problem Description:

 Oracle has been used for a period of time, the connection is disconnected, and an ORA-03135 error is reported.

Problem mining:

This problem also exists when using pl/sql and sqlplus to connect to oracle. It is determined that the problem has nothing to do with the connection method.

Check the server and find that there is no firewall, and the firewall factor is ruled out.

Ping -t server address, found no packet loss, 100% received, the network is smooth.

It is basically certain that the problem lies in the oracle parameter configuration. But other factors are not ruled out.

Solution process:

According to ora-03135 query oracle official solution:

ORA-03135: connection lost contact

Cause: 1) Server unexpectedly terminated or was forced to terminate. 2) Server timed out the connection.

Action: 1) Check if the server session was terminated. 2) Check if the timeout parameters are set properly in sqlnet.ora.

Query relevant information and find that the problem may be related to the parameter SQLNET.EXPIRE_TIME set by sqlnet.ora. Therefore , set the parameter SQLNET.EXPIRE_TIME = 5 in sqlnet.ora on the server (you need to monitor the reload on the server to make the parameter take effect: lsnrctl reload), but do not set this parameter in the client. After waiting for a period of time, the problem does not occur, and the problem is solved.

Knowledge expansion:

Set the SQLNET.EXPIRE_TIME parameter in the sqlnet.ora file on the server side to enable the DCD function. DCD is an abbreviation for Dead Connection Detection, which is used to check for dead but not disconnected sessions.

For example: SQLNET.EXPIRE_TIME = 20 can be set to any value, the unit is minute.

Restart the listener

bash-2.05$ lsnrctl reload

 … The command completed successfully

DCD can be used to prevent firewall timeout.

When a new connection is established, it will read this setting in sqlnet.ora, and when the 20-minute interval arrives, it will send a "probe" SQL*NET packet to the inactive session to confirm whether the client is alive. If it is dead, the corresponding resource will be cleared automatically. If there is a firewall between the client and the server, the firewall has a timeout parameter, for example, if it is set to one hour, when there is no data transmission between the client and the server within one hour, the firewall will disconnect. After enabling DCD, this situation can be avoided by setting the value of SQLNET.EXPIRE_TIME to be smaller than the timeout parameter of the firewall. The "probe" package ensures that the firewall does not disconnect idle sessions.

 In most cases, this operation is fine, if it still does not work, stop the oracle instance and restart the operation

1. Start the database: oracle@suse92:~> sqlplus /nolog SQL*Plus: Release 9.2.0.4.0 - Production on Fri Jan 20 02:29:37 2006 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved . SQL> connect /as sysdba Connected to an idle instance. SQL> startup ORACLE instance started. Total System Global Area 135352820 bytes Fixed Size 455156 bytes Variable Size 109051904 bytes Database Buffers 25165824 bytes mount Redo Buffers 6793

2. Close the database: oracle@suse92:~> sqlplus /nolog SQL*Plus: Release 9.2.0.4.0 - Production on Fri Jan 20 02:29:37 2006 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved . SQL> connect /as sysdba Connected to an idle instance. SQL> shutdwon abort;

3. Start the listener oracle@suse92:~> lsnrctl start

4. Stop the listener oracle@suse92:~> lsnrctl stop

5. Check the listener status 　　lsnrctl">oracle@suse92:~>lsnrctl 　　LSNRCTL> status 　　LSNRCTL> exit

References:

sqlnet.ora http://www.orafaq.com/wiki/Sqlnet.ora

oracle DCD http://www.dbafan.com/blog/?p=174

sqlnet.expire_time and IDLE_TIME http://space.itpub.net/10687595/viewspace-420407

Official description of SQLNET.EXPIRE_TIME

purpose

Use parameter  SQLNET.EXPIRE_TIME to specify a the time interval, in minutes, to send a probe to verify that client/server connections are active. Setting a value greater than 0 ensures that connections are not left open indefinitely, due to an abnormal client termination . If the probe finds a terminated connection, or a connection that is no longer in use, it returns an error, causing the server process to exit. This parameter is primarily intended for the database server, which typically handles multiple connections at any one time .

Limitations on using this terminated connection detection feature are:

· It is not allowed on bequeathed connections.

· Though very small, a probe packet generates additional traffic that may downgrade network performance.

· Depending on which operating system is in use, the server may need to perform additional processing to distinguish the connection probing event from other events that occur. This can also result in degraded network performance.

Default

0

Minimum Value

0

Recommended Value

10

Example

SQLNET.EXPIRE_TIME=10